import pandas as pd
import logging
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import os

class ExcelUtils:
    def __init__(self):
        self.logger = logging.getLogger(__name__)
        self.workbook = None
        self.worksheet = None
        self.df = None

    def load_excel(self, file_path, sheet_name=0):
        """加载Excel文件"""
        try:
            if not os.path.exists(file_path):
                raise FileNotFoundError(f"Excel文件不存在: {file_path}")
            
            # 使用pandas读取Excel，指定openpyxl引擎
            self.df = pd.read_excel(file_path, sheet_name=sheet_name, engine='openpyxl')
            
            # 使用openpyxl加载工作簿
            self.workbook = load_workbook(file_path)
            if isinstance(sheet_name, int):
                self.worksheet = self.workbook.worksheets[sheet_name]
            else:
                self.worksheet = self.workbook[sheet_name]
                
            return True
        except Exception as e:
            self.logger.error(f"加载Excel文件失败: {str(e)}")
            return False

    def get_row_count(self):
        """获取行数"""
        if self.df is not None:
            return len(self.df) + 1  # 加1包含表头
        return 0

    def get_cell_value(self, row, col):
        """获取单元格值"""
        try:
            if isinstance(col, int):
                col = get_column_letter(col)
            return self.worksheet[f"{col}{row}"].value
        except Exception as e:
            self.logger.error(f"获取单元格值失败: {str(e)}")
            return None

    def set_cell_value(self, row, col, value):
        """设置单元格值"""
        try:
            if isinstance(col, int):
                col = get_column_letter(col)
            self.worksheet[f"{col}{row}"] = value
            return True
        except Exception as e:
            self.logger.error(f"设置单元格值失败: {str(e)}")
            return False

    def save_excel(self, file_path=None):
        """保存Excel文件"""
        try:
            save_path = file_path or self.workbook.path
            self.workbook.save(save_path)
            return True
        except Exception as e:
            self.logger.error(f"保存Excel文件失败: {str(e)}")
            return False

    def close_excel(self):
        """关闭Excel文件"""
        if self.workbook:
            self.workbook.close()
            self.workbook = None
            self.worksheet = None
            self.df = None 